package zy.dao.shop.plan.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.plan.PlanDAO;
import zy.dto.shop.plan.PlanChartDto;
import zy.entity.shop.plan.T_Shop_Plan;
import zy.entity.shop.plan.T_Shop_Plan_Expense;
import zy.entity.shop.plan.T_Shop_Plan_Month;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class PlanDAOImpl extends BaseDaoImpl implements PlanDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND pl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND pl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_ar_state"))) {
			sql.append(" AND pl_ar_state = :pl_ar_state ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_shop_code"))) {
			sql.append(" AND pl_shop_code = :pl_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_year"))) {
			sql.append(" AND pl_year = :pl_year ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_number"))) {
			sql.append(" AND INSTR(pl_number,:pl_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Shop_Plan> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pl_id,pl_number,pl_sysdate,pl_date,pl_shop_code,pl_name,pl_year,pl_type,pl_sell_money,pl_us_id,pl_maker,pl_ar_state,pl_ar_date,");
		sql.append(" pl_remark,t.companyid,sp_name AS shop_name");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND pl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND pl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_ar_state"))) {
			sql.append(" AND pl_ar_state = :pl_ar_state ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_shop_code"))) {
			sql.append(" AND pl_shop_code = :pl_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_year"))) {
			sql.append(" AND pl_year = :pl_year ");
		}
		if (StringUtil.isNotEmpty(params.get("pl_number"))) {
			sql.append(" AND INSTR(pl_number,:pl_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if (StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))) {
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY pl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Shop_Plan.class));
	}
	
	@Override
	public List<T_Shop_Plan_Expense> listExpense(String pe_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pe_id,pe_number,pe_expensecode,pe_money,companyid,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_code = pe_expensecode AND pp.companyid = t.companyid LIMIT 1) AS pe_expensename");
		sql.append(" FROM t_shop_plan_expense t");
		sql.append(" WHERE pe_number = :pe_number");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("pe_number", pe_number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Shop_Plan_Expense.class));
	}
	
	@Override
	public List<T_Shop_Plan_Month> listMonth(String pm_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pm_id,pm_number,pm_year,pm_month,pm_sell_money_plan,pm_sell_money_pre,pm_sell_money_real,pm_remark,companyid");
		sql.append(" FROM t_shop_plan_month");
		sql.append(" WHERE pm_number = :pm_number");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("pm_number", pm_number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Shop_Plan_Month.class));
	}

	@Override
	public T_Shop_Plan load(Integer pl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pl_id,pl_number,pl_sysdate,pl_date,pl_shop_code,pl_name,pl_year,pl_type,pl_gross_profit_rate,pl_sell_grow_percent,");
		sql.append(" pl_expense_grow_percent,pl_breakeven_grow_percent,pl_sell_money,pl_cost_money,pl_gross_money,pl_net_money,pl_breakeven,pl_expense,");
		sql.append(" pl_sell_money_pre,pl_cost_money_pre,pl_gross_money_pre,pl_net_money_pre,pl_breakeven_pre,pl_expense_pre,pl_gross_profit_rate_pre,");
		sql.append(" pl_us_id,pl_ar_state,pl_ar_date,pl_remark,pl_maker,companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = pl_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name");
		sql.append(" FROM t_shop_plan t");
		sql.append(" WHERE pl_id = :pl_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("pl_id", pl_id),
					new BeanPropertyRowMapper<>(T_Shop_Plan.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Plan check(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pl_id,pl_number,pl_shop_code,pl_us_id,pl_ar_state,pl_ar_date,companyid");
		sql.append(" FROM t_shop_plan");
		sql.append(" WHERE 1=1");
		sql.append(" AND pl_number = :pl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pl_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Plan.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Plan check(String shop_code, Integer year, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pl_id,pl_ar_state");
		sql.append(" FROM t_shop_plan");
		sql.append(" WHERE 1=1");
		sql.append(" AND pl_year = :pl_year");
		sql.append(" AND pl_shop_code = :pl_shop_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pl_year", year).addValue("pl_shop_code", shop_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Plan.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Shop_Plan loadPreData(String shop_code, Integer year, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT IFNULL(SUM(shl_money),0) AS pl_sell_money_pre,");
		sql.append(" IFNULL(SUM(shl_cost_price*shl_amount),0) AS pl_cost_money_pre");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND YEAR(shl_date) = :year");
		sql.append(" AND companyid = :companyid");
		T_Shop_Plan plan =  namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("year", year).addValue("shop_code", shop_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_Plan.class));
		plan.setPl_gross_money_pre(plan.getPl_sell_money_pre()-plan.getPl_cost_money_pre());
		
		if(plan.getPl_sell_money_pre().equals(0d)){
			return null;
		}
		//查询费用
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(ep_money),0) AS pl_expense_pre");
		sql.append(" FROM t_money_expense");
		sql.append(" WHERE 1=1");
		sql.append(" AND ep_ar_state = 1");
		sql.append(" AND YEAR(ep_date) = :year");
		sql.append(" AND ep_shop_code = :shop_code");
		sql.append(" AND companyid = :companyid");
		Double pl_expense_pre = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("year", year).addValue("shop_code", shop_code).addValue("companyid", companyid), Double.class);
		plan.setPl_expense_pre(pl_expense_pre);
		
		plan.setPl_net_money_pre(plan.getPl_gross_money_pre()-plan.getPl_expense_pre());
		if(!plan.getPl_sell_money_pre().equals(0d)){
			plan.setPl_gross_profit_rate_pre(Double.parseDouble(String.format("%.2f", plan.getPl_gross_money_pre()/plan.getPl_sell_money_pre())));
		}else {
			plan.setPl_gross_profit_rate_pre(0d);
		}
		if(!plan.getPl_gross_profit_rate_pre().equals(0d)){
			plan.setPl_breakeven_pre(Double.parseDouble(String.format("%.2f", plan.getPl_expense_pre()/plan.getPl_gross_profit_rate_pre())));
		}else {
			plan.setPl_breakeven_pre(0d);
		}
		return plan;
	}
	
	@Override
	public List<Map<String, Object>> loadPreDataMonth(String shop_code, Integer year, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT MONTH(shl_date) month, SUM(shl_money) AS money");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND YEAR(shl_date) = :year");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY MONTH(shl_date)");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), 
				new MapSqlParameterSource().addValue("year", year).addValue("shop_code", shop_code).addValue("companyid", companyid));
	}
	
	@Override
	public List<Map<String, Object>> loadPreDataDay(String shop_code, Integer year, Integer month, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DAY(shl_date) day, SUM(shl_money) AS money");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code = :shop_code");
		sql.append(" AND YEAR(shl_date) = :year");
		sql.append(" AND MONTH(shl_date) = :month");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY DAY(shl_date)");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), 
				new MapSqlParameterSource().addValue("year", year).addValue("month", month).addValue("shop_code", shop_code).addValue("companyid", companyid));
	}

	@Override
	public void save(T_Shop_Plan plan, List<T_Shop_Plan_Month> planMonths, List<T_Shop_Plan_Expense> planExpenses) {
		String prefix = CommonUtil.NUMBER_PREFIX_SHOP_PLAN + DateUtil.getYearMonthDateYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(pl_number))) AS new_number");
		sql.append(" FROM t_shop_plan");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(pl_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", plan.getCompanyid()), String.class);
		plan.setPl_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_plan");
		sql.append(" (pl_number,pl_sysdate,pl_date,pl_shop_code,pl_name,pl_year,pl_type,pl_gross_profit_rate,pl_sell_grow_percent,pl_expense_grow_percent,");
		sql.append(" pl_breakeven_grow_percent,pl_sell_money,pl_cost_money,pl_gross_money,pl_net_money,pl_breakeven,pl_expense,pl_sell_money_pre,");
		sql.append(" pl_cost_money_pre,pl_gross_money_pre,pl_net_money_pre,pl_breakeven_pre,pl_expense_pre,pl_gross_profit_rate_pre,pl_us_id,");
		sql.append(" pl_ar_state,pl_ar_date,pl_remark,pl_maker,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pl_number,:pl_sysdate,:pl_date,:pl_shop_code,:pl_name,:pl_year,:pl_type,:pl_gross_profit_rate,:pl_sell_grow_percent,:pl_expense_grow_percent,");
		sql.append(" :pl_breakeven_grow_percent,:pl_sell_money,:pl_cost_money,:pl_gross_money,:pl_net_money,:pl_breakeven,:pl_expense,:pl_sell_money_pre,");
		sql.append(" :pl_cost_money_pre,:pl_gross_money_pre,:pl_net_money_pre,:pl_breakeven_pre,:pl_expense_pre,:pl_gross_profit_rate_pre,:pl_us_id,");
		sql.append(" :pl_ar_state,:pl_ar_date,:pl_remark,:pl_maker,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(plan),holder);
		plan.setPl_id(holder.getKey().intValue());
		//计划到月明细
		for (T_Shop_Plan_Month item : planMonths) {
			item.setPm_year(plan.getPl_year());
			item.setCompanyid(plan.getCompanyid());
			item.setPm_number(plan.getPl_number());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_plan_month");
		sql.append(" (pm_number,pm_year,pm_month,pm_sell_money_plan,pm_sell_money_pre,pm_sell_money_real,pm_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pm_number,:pm_year,:pm_month,:pm_sell_money_plan,:pm_sell_money_pre,:pm_sell_money_real,:pm_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(planMonths.toArray()));
		//费用开支明细
		if (planExpenses != null && planExpenses.size() > 0) {
			for (T_Shop_Plan_Expense item : planExpenses) {
				item.setCompanyid(plan.getCompanyid());
				item.setPe_number(plan.getPl_number());
			}
			sql.setLength(0);
			sql.append(" INSERT INTO t_shop_plan_expense");
			sql.append(" (pe_number,pe_expensecode,pe_money,companyid)");
			sql.append(" VALUES");
			sql.append(" (:pe_number,:pe_expensecode,:pe_money,:companyid)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(planExpenses.toArray()));
		}
	}
	
	@Override
	public void updateApprove(T_Shop_Plan plan) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_plan");
		sql.append(" SET pl_ar_state=:pl_ar_state");
		sql.append(" ,pl_ar_date = :pl_ar_date");
		sql.append(" WHERE pl_id=:pl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(plan));
	}
	
	@Override
	public void del(String pl_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_plan");
		sql.append(" WHERE pl_number=:pl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pl_number", pl_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_plan_month");
		sql.append(" WHERE pm_number=:pl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pl_number", pl_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_shop_plan_expense");
		sql.append(" WHERE pe_number=:pl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pl_number", pl_number).addValue("companyid", companyid));
	}
	
	@Override
	public List<PlanChartDto> chartByShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pl_shop_code AS shop_code,sp_name AS shop_name,pl_sell_money AS sell_money_plan,");
		sql.append(" IFNULL((SELECT SUM(shl_money) FROM t_sell_shoplist shl WHERE YEAR(shl_date) = pl_year AND shl_shop_code = pl_shop_code ");
		sql.append(" AND shl.companyid = t.companyid),0) AS sell_money_real");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("pl_shop_code"))) {
			sql.append(" AND pl_shop_code = :pl_shop_code ");
		}
		sql.append(" AND pl_ar_state = 1 ");
		sql.append(" AND pl_year = :pl_year");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(PlanChartDto.class));
	}
	
	@Override
	public List<PlanChartDto> chartByMonth(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT month,SUM(sell_money_plan) AS sell_money_plan,SUM(sell_money_real) AS sell_money_real");
		sql.append(" FROM(");
		sql.append(" SELECT pm_month AS month,IFNULL(SUM(pm_sell_money_plan),0) AS sell_money_plan,");
		sql.append(" IFNULL((SELECT SUM(shl_money) FROM t_sell_shoplist shl WHERE YEAR(shl_date) = pl_year AND MONTH(shl_date) = pm_month ");
		sql.append(" AND shl_shop_code = pl_shop_code AND shl.companyid = t.companyid),0) AS sell_money_real");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN t_shop_plan_month pm ON pm_number = pl_number AND pm.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("pl_shop_code"))) {
			sql.append(" AND pl_shop_code = :pl_shop_code ");
		}
		sql.append(" AND pl_ar_state = 1 ");
		sql.append(" AND pl_year = :pl_year");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pm_month,pl_shop_code) t");
		sql.append(" GROUP BY month");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(PlanChartDto.class));
	}
	
	@Override
	public Map<String, Object> statByYearMonth(Map<String, Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT IFNULL(SUM(planmoney_year),0) AS planmoney_year,IFNULL(SUM(realmoney_year),0) AS realmoney_year");
		sql.append(" FROM(");
		sql.append(" SELECT pl_sell_money AS planmoney_year,");
		sql.append(" IFNULL((SELECT SUM(shl_money) FROM t_sell_shoplist shl WHERE YEAR(shl_date) = pl_year AND shl_shop_code = pl_shop_code");
		sql.append(" AND shl.companyid = t.companyid),0) AS realmoney_year");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND pl_ar_state = 1");
		sql.append(" AND pl_year = :year");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" )temp");
		resultMap.putAll(namedParameterJdbcTemplate.queryForMap(sql.toString(), params));
		sql.setLength(0);
		
		sql.append(" SELECT IFNULL(SUM(planmoney_month),0) AS planmoney_month,IFNULL(SUM(realmoney_month),0) AS realmoney_month");
		sql.append(" FROM(");
		sql.append(" SELECT pm_sell_money_plan AS planmoney_month,");
		sql.append(" IFNULL((SELECT SUM(shl_money) FROM t_sell_shoplist shl WHERE YEAR(shl_date) = pl_year AND MONTH(shl_date) = pm_month");
		sql.append(" AND shl_shop_code = pl_shop_code AND shl.companyid = t.companyid),0) AS realmoney_month");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pl_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN t_shop_plan_month pm ON pm_number = pl_number AND pm.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND pl_ar_state = 1");
		sql.append(" AND pl_year = :year");
		sql.append(" AND pm_month = :month");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" )temp");
		resultMap.putAll(namedParameterJdbcTemplate.queryForMap(sql.toString(), params));
		return resultMap;
	}
	
	@Override
	public T_Shop_Plan_Month loadPlanMonth(String shop_code,Integer year,Integer month,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pm_id,pm_number,pm_year,pm_month,pm_sell_money_plan,pm_sell_money_pre,pm_sell_money_real,pm_remark,t.companyid");
		sql.append(" FROM t_shop_plan t");
		sql.append(" JOIN t_shop_plan_month pm ON pl_number = pm_number AND pm.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pl_shop_code = :shop_code");
		sql.append(" AND pm_year = :year");
		sql.append(" AND pm_month = :month");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("year", year).addValue("month", month).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Shop_Plan_Month.class));
		} catch (Exception e) {
			return null;
		}
	}
	
}
